package cnu.cse.crawler.manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cnu.cse.crawler.xedata.XeAttachedFile;
import cnu.cse.crawler.xedata.XeDocument;

public class DatabaseManager 
{

	///////////////////////////////////////////// Member /////////////////////////////////////////////
	//
	//
	
	private static DatabaseManager instance = new DatabaseManager();
	
	private String attachedBinariesRelativePath = "./files/attache/binaries";
	private String attachedImagesRelativePath = "./files/attache/images";
	
	private boolean isConnected = false;
	private Connection conn = null;

	
	
	
	
	
	////////////////////////////////// Constructor and Initializer ///////////////////////////////////
	//
	//
	
	private DatabaseManager()
	{
		
	}
	
	public static DatabaseManager getInstance()
	{
		return instance;
	}
	
	public void initAttachedFileRelativePath(String attachedBinariesRelativePath, String attachedImagesRelativePath)
	{
		this.attachedBinariesRelativePath = attachedBinariesRelativePath;
		this.attachedImagesRelativePath = attachedImagesRelativePath;
	}
	
	
	
	
	
	
	//////////////////////////////////////// Private Methods ////////////////////////////////////////
	//
	//
	
	
	
	
	
	
	//////////////////////////////////////// Public Methods ////////////////////////////////////////
	//
	//
		
	public boolean connectDatabase(String url, String id, String password)
	{
		try 
		{
			if (isConnected == false)
			{
				conn = DriverManager.getConnection(url, id, password);
				isConnected = true;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return false;
		}
		
		return isConnected;
	}	
	
	public String retrieveDatabaseVersion() throws SQLException
	{
		String query = "SELECT version()";
		
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		
		String result = null;
		if (rs.next())
		{
			result = rs.getString(1);
		}
				
		return result;
	}
	
	public Long retrieveLastXeSequence()  throws SQLException
	{
		String query = "SELECT seq FROM xe_sequence ORDER BY seq DESC LIMIT 1";
		
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);

		Long result = null;
		if (rs.next())
		{
			result = (Long) rs.getObject(1);
		}
		
		return result;
	}
	
	public Long getXeSequence()  throws SQLException
	{
		String query = "INSERT INTO xe_sequence (seq) VALUES (NULL)";
		
		PreparedStatement pst = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
		pst.executeUpdate();
		ResultSet rs = pst.getGeneratedKeys();
		
		Long result = null;
		if (rs.next())
		{
			result = (Long) rs.getObject(1);
		}
				
		return result;
	}
	
	public int insertXeDocument(Long moduleSerial, Long documentSerial, XeDocument document) throws SQLException
	{
		String q1 = "INSERT INTO xe_documents (document_srl, module_srl, category_srl, lang_code, is_notice, title, title_bold, title_color, content, readed_count, voted_count, blamed_count, comment_count, trackback_count, uploaded_count, password, user_id, user_name, nick_name, member_srl, email_address, homepage, tags, extra_vars, regdate, last_update, last_updater, ipaddress, list_order, update_order, allow_trackback, notify_message, status, comment_status)";
		String q2 = " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String query = q1 + q2;
		
		PreparedStatement pst = conn.prepareStatement(query);
		
		// document_srl
		pst.setLong(1, documentSerial);
		
		// module_srl
		pst.setLong(2, moduleSerial);
		
		// category_srl
		pst.setLong(3, 0L);
		
		// lang_code
		pst.setString(4, "ko");
		
		// is_notice
		if (document.getType() == XeDocument.TYPE_NOTICE)
		{
			pst.setString(5, "Y");
		}
		else
		{
			pst.setString(5, "N");
		}
		
		// title
		pst.setString(6, document.getTitle());
		
		// title_bold
		pst.setString(7, "N");
		
		// title_color
		pst.setString(8, "N");
		
		//content
		pst.setString(9, document.getContents());
		
		// readed_count
		pst.setLong(10, document.getReadCount());
		
		// voted_count
		pst.setLong(11, 0L);
		
		// blamed_count
		pst.setLong(12, 0L);
		
		// comment_count
		pst.setLong(13, document.getCommentCount());
		
		// trackback_count
		pst.setLong(14, 0L);
		
		// uploaded_count
		pst.setLong(15, document.getAttachedFileCount());
		
		// password
		pst.setString(16, null);
		
		// user_id
		pst.setString(17, document.getWriterId());
		
		// user_name
		pst.setString(18, document.getWriterNickname());
		
		// nick_name
		pst.setString(19, document.getWriterNickname());
		
		// member_srl
		pst.setLong(20, 4L);
		
		// email_address
		pst.setString(21, document.getWriterId() + "@cnu.ac.kr");
		
		// homepage
		pst.setString(22, "");
		
		// tags
		pst.setString(23, null);
		
		// extra_vars
		pst.setString(24, "N;");
		
		// regdate
		String datetime = document.getDate() + document.getTime();
		pst.setString(25, datetime);
		
		// last_update
		pst.setString(26, datetime);
		
		// last_updater
		pst.setString(27, null);
		
		// ipaddress
		pst.setString(28, document.getIpAddress());
		
		// list_order
		Long negativeDocumentSerial = -documentSerial;
		pst.setLong(29, negativeDocumentSerial);
		
		// update_order
		pst.setLong(30, negativeDocumentSerial);
		
		// allow_trackback
		pst.setString(31, "N");
		
		// notify_message
		pst.setString(32, "N");
		
		// status
		pst.setString(33, "PUBLIC");
		
		// comment_status
		pst.setString(34, "ALLOW");
		
		int result = pst.executeUpdate();
		
		return result;
	}
	
	public int insertXeFiles(Long moduleSerial, Long documentSerial, Long fileSerial, XeAttachedFile file) throws SQLException
	{
		String q1 = "INSERT INTO xe_files (file_srl, upload_target_srl, upload_target_type, sid, module_srl, member_srl, download_count, direct_download, source_filename, uploaded_filename, file_size, comment, isvalid, regdate, ipaddress)";
		String q2 = " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String query = q1 + q2;
		
		PreparedStatement pst = conn.prepareStatement(query);
		
		// file_srl
		pst.setLong(1, fileSerial);
		
		// upload_target_srl
		pst.setLong(2, documentSerial);
		
		// upload_target_type
		pst.setString(3, null);
		
		// sid
		pst.setString(4, file.getHashFileName());
		
		// module_srl
		pst.setLong(5, moduleSerial);
		
		// member_srl
		pst.setLong(6, 4L);
		
		// download_count
		pst.setLong(7, 0L);
		
		// direct_download
		pst.setString(8, "N");
		
		// source_filename
		pst.setString(9, file.getSourceFileName());
		
		// uploaded_filename
		pst.setString(10, attachedBinariesRelativePath + file.getUploadedFileName());
		
		// file_size
		pst.setLong(11, file.getFileSize());
		
		// comment
		pst.setString(12, null);
		
		// isvalid
		pst.setString(13, "Y");
		
		// regdate
		pst.setString(14, file.getDatetime());
		
		// ipaddress
		pst.setString(15, file.getIpAddress());
				
		int result = pst.executeUpdate();
		
		return result;
	}
	
	
	
}
